Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# HIERHEIGHT command

The HIERHEIGHT command populates a previously-defined relation with the values of a specified hierarchical dimension by level. Typically, you use the HIERHEIGHT command when you are preparing an analytic workspace for access using the `OLAP_TABLE` function.

To retrieve the value of a node (by level) for the value of a hierarchical dimension, use the HIERHEIGHT function.

Syntax

HIERHEIGHT familyrelation [(qdrlist)] INTO{hierheight-relation -

[USING level-relation | A | D] [INHIERARCHY { inh-variable| inh-valueset}]

Arguments

family-relation

  A child-parent self-relation for the hierarchical dimension. This relation can have multiple dimensions; however, one of the dimensions of family-relation must be the hierarchical dimension. The values of the family-relation are the values of the hierarchical dimension that is the parent of each set of dimension values

qdrlist

  A list of QDRs that limits the values of family-relation. Specify the QDRs as described in "Form of a Qualified Data Reference". When you do not specify a value for qdrlist, HIERHEIGHT uses the values of family-relation that are in current status.

hierheight-relation

  A previously -defined relation that the HIERHEIGHT command populates when it executes. This relation can have multiple dimensions; however, it must be dimensioned by the dimensions of family-relation and one other dimension that represents the levels of the hierarchical dimension. The actual construct of the dimension that represents the levels of the hierarchical dimension varies depending on whether or not the HIERHEIGHT statement includes the USING phrase:

  - When the statement includes the USING phrase, the dimension that represents the levels of the hierarchical dimension is a dimension that contains the names of the levels.

  - When the statement does not include the USING phrase, the dimension that represents the levels of the hierarchical dimension is an INTEGER dimension that has as values the depth of the level.

  When hierheight-relation is populated before a HIERHEIGHT statement executes, the HIERHEIGHT statement depopulates it before computing new values.

USING level-relation

  Specifies that the relation is populated using the specified level-relation. A level-relation is a relation that is a dimensioned by the hierarchical dimension and (when the hierarchical dimension is a multi-hierarchical dimension) by a dimension that is the names of the hierarchies. The values of the relation are values of a dimension that represents the levels of the hierarchy. This dimension typically is a TEXT or ID dimension that has the names of the levels as values.

A

  Specifies that the relation is populated in ascending order.

D

  (Default) Specifies that the relation is populated in descending order.

inh-variable

  A BOOLEAN variable that is dimensioned by the hierarchical dimension and, when the hierarchical dimension is a multi-hierarchical dimension, by a dimension that is the names of the hierarchies. The values of the variable are `TRUE` when the dimension value is in a hierarchy and `FALSE` when it is not.

inh-valueset

  The name of a valueset object whose values are the hierarchical dimension values to be considered when creating grouping ids. Values not included in the valueset are ignored.

Notes

HIERHEIGHT with the OLAP_TABLE Function

Typically, you use a HIERHEIGHT statement when you are preparing an analytic workspace for access using the `OLAP_TABLE` function.

Examples

Example 14-10 Creating a Relational Representation of a Geography Hierarchy

Assume that there is an analytic workspace named `myaw` that has a Geography hierarchy defined with analytic objects with the following definitions.

    DEFINE geog.hierdim DIMENSION TEXT
    LD Hierarchy names for Geography hierarchies

    DEFINE geog.leveldim DIMENSION TEXT
    LD List of levels for GEOGRAPHY hierarchies

    DEFINE geography DIMENSION TEXT WIDTH 12
    LD Values for the Geography hierarchies

    DEFINE geog.levelrel RELATION geog.leveldim <geography geog.hierdim>
    LD Level of each value in the Geography hierarchies

    DEFINE geog.parent RELATION geography <geography geog.hierdim>
    LD Child-parent relation for the Geography hierarchies

    DEFINE geog.familyrel RELATION geography <geography geog.leveldim geog.hierdim>
    LD Geography values by level and hierarchy

These objects have the following structures.

    GEOGRAPHY
    ------------------
    World
    Americas
    Canada
    USA
    Toronto
    Montreal
    Boston
    LosAngeles

    GEOG.HIERDIM
    ------------------
    Standard
    Consolidated

    GEOG.LEVELDIM
    ------------------
    World
    Continent
    Country
    City
    Consolidated
    Continent
    Consolidated
    Country

                       ------------GEOG.LEVELREL------------
                       ------------GEOG.HIERDIM-------------
    GEOGRAPHY               Standard         Consolidated
    ------------------ ------------------ ------------------
    World              World              NA
    Americas           Continent          Consolidated
                                          Continent
    Canada             Country            Consolidated
                                          Country
    USA                Country            Consolidated
                                          Country
    Toronto            City               NA
    Montreal           City               NA
    Boston             City               NA
    LosAngeles         City               NA

                       -------------GEOG.PARENT-------------
                       ------------GEOG.HIERDIM-------------
    GEOGRAPHY               Standard         Consolidated
    ------------------ ------------------ ------------------
    World              NA                 NA
    Americas           World              NA
    Canada             Americas           Americas
    USA                Americas           Americas
    Toronto            Canada             NA
    Montreal           Canada             NA
    Boston             USA                NA
    LosAngeles         USA                NA

To create a family relation of the Geography hierarchy you define an analytic workspace object with the following definition.

    DEFINE geog.familyrel RELATION geography <geography geog.leveldim geog.hierdim>
    LD Geography values by level and hierarchy

Then you use a HIERHEIGHT statement as illustrated in the following statement to populate the object.

    HIERHEIGHT geog.parent INTO geog.familyrel USING geog.levelrel

By issuing a REPORT statement, you can display the relational representations of both the `Standard` and `Consolidated` hierarchies of the `geography` dimension.

    REPORT DOWN geography geog.familyrel

    GEOG.HIERDIM: Standard
                 -------------------------------GEOG.FAMILYREL--------------------------------
                 --------------------------------GEOG.LEVELDIM--------------------------------
                                                                     Consolidated Consolidated
    GEOGRAPHY       World      Continent     Country        City      Continent     Country
    ------------ ------------ ------------ ------------ ------------ ------------ ------------
    World        World        NA           NA           NA           NA           NA
    Americas     World        Americas     NA           NA           NA           NA
    Canada       World        Americas     Canada       NA           NA           NA
    USA          World        Americas     USA          NA           NA           NA
    Toronto      World        Americas     Canada       Toronto      NA           NA
    Montreal     World        Americas     Canada       Montreal     NA           NA
    Boston       World        Americas     USA          Boston       NA           NA
    LosAngeles   World        Americas     USA          LosAngeles   NA           NA

    GEOG.HIERDIM: Consolidated
                 -------------------------------GEOG.FAMILYREL--------------------------------
                 --------------------------------GEOG.LEVELDIM--------------------------------
                                                                     Consolidated Consolidated
    GEOGRAPHY       World      Continent     Country        City      Continent     Country
    ------------ ------------ ------------ ------------ ------------ ------------ ------------
    World        NA           NA           NA           NA           NA           NA
    Americas     NA           NA           NA           NA           Americas     NA
    Canada       NA           NA           NA           NA           Americas     Canada
    USA          NA           NA           NA           NA           Americas     USA
    Toronto      NA           NA           NA           NA           NA           NA
    Montreal     NA           NA           NA           NA           NA           NA
    Boston       NA           NA           NA           NA           NA           NA
    LosAngeles   NA           NA           NA           NA           NA           NA